﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace PUZZLEgestServer.DAL
{
    public static class DataAccess
    {
        #region Properties
        
        private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();

        /// <summary>
        /// Returns the super admin connection string, using the user and password stored in the registry and the DB details stored in the app config
        /// </summary>
        public static string SuperAdminConnectionString
        {
            get 
            {
                try
                {
                    //get the user and password
                    string user = Util.Globals.ReadRegistryKey("user", true);
                    string password = Util.Globals.ReadRegistryKey("password", true);
                    string server = ConfigurationManager.AppSettings.Get("DBServer");
                    string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");
                    return string.Format("data source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;App=EntityFramework", server, catalog, user, password);
                    //return @"data source=PC0085\SQLEXPRESS;initial catalog=puzzlegestv2;user id=alex;password=alex;multipleactiveresultsets=True;App=EntityFramework";
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }

        private static DataSet dbSchema;
        public static DataSet DBSchema
        {
            get
            {
                if (dbSchema == null)
                    dbSchema = GetDataBaseSchema();

                return dbSchema;
            }
        }

        #endregion

        #region Methods

        public static DataTable GetUserPermissions(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new Exception("User name is mandatory");

            string query = "SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] " +
                            "FROM [sys].[database_permissions] sec " +
                              "JOIN [sys].[database_principals] prin " +
                                "ON sec.[grantee_principal_id] = prin.[principal_id] " +
                            "WHERE prin.name = '" + userName + "' " +
                            "ORDER BY [User], [Permission];";

            SqlConnection conn = new SqlConnection(DataAccess.SuperAdminConnectionString);
            conn.Open();
            return Select(conn, query);
        }

        /// <summary>
        /// Selects data from the database using a query and sql parameters. Returns a data table or null if error occurs.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static DataTable Select(SqlTransaction transaction, string query, params SqlParameter[] sqlParams)
        {
            DataTable dt = new DataTable();
            using (SqlCommand command = new SqlCommand(query, transaction.Connection, transaction))
            {
                //add the parameters
                if (sqlParams.Count() > 0)
                {
                    //add the param
                    foreach (SqlParameter p in sqlParams)
                    {
                        command.Parameters.Add(p);
                    }
                }

                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.Fill(dt);
                }
            }
            return dt;
        }

        public static DataTable Select(SqlConnection connection, string query, params SqlParameter[] sqlParams)
        {

            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                DataTable dt = Select(transaction, query, sqlParams);

                transaction.Commit();

                return dt;
            }
            catch (System.Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
        }

        /// <summary>
        /// Executes queries on the database using an implicit transaction object (that will be comited inside the method) and sql paramaters. Return the last index inserted or -1 if error occurs.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int ExecuteScalarInsert(SqlConnection connection, string query, params SqlParameter[] sqlParams)
        {
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                int ai_id = ExecuteScalarInsert(transaction, query, sqlParams);

                transaction.Commit();

                return ai_id;
            }
            catch (System.Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
        }

        /// <summary>
        /// Executes INSERT queries on the database using a transaction object and sql paramaters. Return the last index inserted or -1 if error occurs.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="query"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int ExecuteScalarInsert(SqlTransaction transaction, string query, params SqlParameter[] sqlParams)
        {

            if (transaction == null)
                return -1;

            //add the Scope Identity
            if (!query.EndsWith(";"))
                query += ";";
            query += " Select Scope_Identity()";

            using (SqlCommand command = new SqlCommand(query, transaction.Connection, transaction))
            {
                //add the parameters
                if (sqlParams.Count() > 0)
                {
                    //add the param
                    foreach (SqlParameter p in sqlParams)
                    {
                        command.Parameters.Add(p);
                    }
                }

                //execute query
                object result = command.ExecuteScalar();
                return Convert.ToInt16(result);
            }

        }


        /// <summary>
        /// Executes queries on the database using an implicit transaction object (that will be comited inside the method) and sql paramaters. Return the last index inserted or -1 if error occurs.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int ExecuteScalar(SqlConnection connection, string query, params SqlParameter[] sqlParams)
        {
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                int rowsAffected = ExecuteScalar(transaction, query, sqlParams);

                transaction.Commit();

                return rowsAffected;
            }
            catch (System.Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }

        }

        /// <summary>
        /// Executes UPDATE and DELETE queries on the database using a transaction object and sql paramaters. Returns the number of rows affected.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="query"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int ExecuteScalar(SqlTransaction transaction, string query, params SqlParameter[] sqlParams)
        {
            if (transaction == null)
                return -1;

            int rowsAffected = 0;
            using (SqlCommand command = new SqlCommand(query, transaction.Connection, transaction))
            {
                //add the parameters
                if (sqlParams.Count() > 0)
                {
                    //add the param
                    foreach (SqlParameter p in sqlParams)
                    {
                        command.Parameters.Add(p);
                    }
                }

                //execute query
                object result = command.ExecuteScalar();
                if (result != null && result is int)
                    return (int)result;
                else
                    return 0;

            }

            return rowsAffected;

        }

        /// <summary>
        /// Reads the database schema into a data set using the super admin credentials
        /// </summary>
        /// <returns></returns>
        static private DataSet GetDataBaseSchema()
        {
            System.Data.OleDb.OleDbConnection oleconn = null;
            try
            {
                oleconn = new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;" + DataAccess.SuperAdminConnectionString + ";Integrated Security=SSPI");
                oleconn.Open();

                DataSet ds = new DataSet("DBSchema");
                DataTable dtTables = oleconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DataTable dtSysTables = dtTables.Clone();
                dtSysTables.TableName = "_Tables";

                foreach (DataRow dr in dtTables.Rows)
                {
                    if (dr["TABLE_NAME"].ToString() != "sysdiagrams")
                    {
                        dtSysTables.ImportRow(dr);

                        DataTable dt = oleconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, dr["TABLE_NAME"].ToString(), null });
                        dt.TableName = dr["TABLE_NAME"].ToString();
                        ds.Merge(dt);
                    }
                }

                ds.Merge(dtSysTables);

                return ds;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (oleconn != null)
                    oleconn.Close();
            }
            return null;
        }

        #endregion
    }
}
